package com.ls.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ls.domain.Score;
import com.ls.domain.vo.ReportScore;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ScoreMapper extends BaseMapper<Score> {

    //教师查询学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass}\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> selectScoreTch(String teacherClass, int startIndex, int pageSize);

    //教师查询学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass};")
    int selectScoreTchCount(String teacherClass);

    //教师搜索学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass}\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "AND student.sno LIKE concat('%',#{sno},'%')\n" +
            "AND student.student_name LIKE concat('%',#{studentName},'%')\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> searchScoreTch(String teacherClass, String reportName, String sno, String studentName, int startIndex, int pageSize);

    //教师搜索学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass}\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "AND student.sno LIKE concat('%',#{sno},'%')\n" +
            "AND student.student_name LIKE concat('%',#{studentName},'%');")
    int searchScoreTchCount(String teacherClass, String reportName, String sno, String studentName);

    //管理员查询学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> selectScoreAdm(int startIndex, int pageSize);

    //管理员查询学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1;")
    int selectScoreAdmCount();

    //管理员搜索学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class LIKE concat('%',#{studentClass},'%')\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "AND student.sno LIKE concat('%',#{sno},'%')\n" +
            "AND student.student_name LIKE concat('%',#{studentName},'%')\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> searchScoreAdm(String studentClass, String reportName, String sno, String studentName, int startIndex, int pageSize);

    //管理员搜索学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{studentClass}\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "AND student.sno LIKE concat('%',#{sno},'%')\n" +
            "AND student.student_name LIKE concat('%',#{studentName},'%');")
    int searchScoreAdmCount(String studentClass, String reportName, String sno, String studentName);

    //学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.id = #{sid}\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> selectScoreStu(Integer sid,int startIndex, int pageSize);

    //学生成绩 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.id = #{sid};")
    int selectScoreStuCount(Integer sid);

    //学生成绩搜索 注解sql: 当前页面、页面大小
    @Select("SELECT report.id, report.report_name, report.report_question, \n" +
            "student.id as sid, student.sno, student.student_name, student.student_class,\n" +
            "answer.report_answer, answer.answer_finish, \n" +
            "score.score_points, score.report_score, score.score_remark, score.score_comment\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.id = #{sid}\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "LIMIT #{startIndex}, #{pageSize};")
    List<ReportScore> searchScoreStu(Integer sid,String reportName,int startIndex, int pageSize);

    //学生成绩搜索 注解sql: 当前页面、页面大小
    @Select("SELECT COUNT(*)\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND report.report_name LIKE concat('%',#{reportName},'%')\n" +
            "AND answer.answer_finish = 1 AND student.id = #{sid};")
    int searchScoreStuCount(Integer sid,String reportName);

    //教师分析成绩
    @Select("SELECT score.report_score\n" +
            "FROM report, student, answer, score \n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid AND report.id = score.rid AND student.id = score.sid \n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass} ;")
    List<String> analyseScoreTch(String teacherClass);

    //教师导出成绩
    @Select("SELECT report.report_name, report.report_question, \n" +
            "student.sno, student.student_name, student.student_class,\n" +
            "score.report_score\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 AND student.student_class = #{teacherClass} ;")
    List<ReportScore> inputScoreTch(String teacherClass);

    //管理员端导出成绩
    @Select("SELECT report.report_name, report.report_question, \n" +
            "student.sno, student.student_name, student.student_class,\n" +
            "score.report_score\n" +
            "FROM report, student, answer, score\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid\n" +
            "AND report.id = score.rid AND student.id = score.sid\n" +
            "AND answer.answer_finish = 1 ;")
    List<ReportScore> inputScoreAdm();

    //导出学生报告为word
    @Select("SELECT report.report_name, report.report_question, student.sno, \n" +
            "student.student_name, student.student_class, answer.report_answer\n" +
            "FROM report, student, answer\n" +
            "WHERE report.id = answer.rid AND student.id = answer.sid \n" +
            "AND answer.answer_finish = 1 AND student.id = #{sid} AND report.id = #{rid};")
    ReportScore reportInput(Integer sid, Integer rid);
}
